package com.pcjavanet.collect.mes.dao.impl.jdbc.postgresql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.pcjavanet.collect.mes.dao.AbstractDao;
import com.pcjavanet.collect.mes.dao.ActivityDataDao;
import com.pcjavanet.collect.mes.to.ActivityData;
import com.pcjavanet.collect.mes.util.CommonServerUtil;
 

/**
 * class ActivityDataDaoImpl.
 * @version 4.0
 * @author jerry_xia
 * @date May 17,2012
 */
public class ActivityDataDaoImpl extends AbstractDao implements ActivityDataDao{

	protected static Logger logger = Logger.getLogger(ActivityDataDaoImpl.class);
 
	private static final String SELECT_ACTIVITY_BY_DATA_KEY_AND_CHART_TYPE_SQL = "select *, to_timestamp(a_datetime)  #REPLACE#  as time   from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_activity where spc_data_key=? and chart_type=? ";
	/**
	 * get all activity data by data key and chart type.
	 * @param dataId -int
	 * @param chartType -String
	 * @return List<ActivityData> - activityDataList
	 * @throws DataAccessException
	 */
	public List<ActivityData> getAllActivityByDataKeyAndChartType(int monitorId, final int dataId, final String chartType,String netuserId ) throws DataAccessException {
		final String timezoneNumber =  CommonServerUtil.getTimeZoneNumberStr( netuserId );
		String timezone = CommonServerUtil.getSfdcTimeZoneForSql( netuserId ,ActivityDataDaoImpl.class.getName()+"_getAllActivityByDataKeyAndChartType");
		String sql = SELECT_ACTIVITY_BY_DATA_KEY_AND_CHART_TYPE_SQL.replace("#REPLACE#", timezone);
		final List<ActivityData> activityDataList  = new ArrayList<ActivityData>();
		jdbcTemplate.query(sql, 
		new PreparedStatementSetter() {
		    public void setValues(PreparedStatement ps)	throws SQLException {
				ps.setInt(1, dataId);
				ps.setString(2, chartType);
			}
		},
		new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				activityDataList.add(initActivityData(rs,timezoneNumber) );
			}
		});
		return activityDataList;
	}
	
	/**
	 * get current page activity data by data key and chart type.
	 * @param start -int
	 * @param limit -int
	 * @param dataId -int
	 * @param chartType -String
	 * @return List<ActivityData> - activityDataList
	 * @throws DataAccessException
	 */
	public List<ActivityData> getCurrentPageActivityByDataKeyAndChartType(int monitorId,int start,int limit, final int dataId, final String chartType,String netuserId ) throws DataAccessException {
		final List<ActivityData> activityDataList  = new ArrayList<ActivityData>();
		final String timezoneNumber =  CommonServerUtil.getTimeZoneNumberStr( netuserId );
		String timezone = CommonServerUtil.getSfdcTimeZoneForSql( netuserId ,ActivityDataDaoImpl.class.getName()+"_getCurrentPageActivityByDataKeyAndChartType");
		String sql = SELECT_ACTIVITY_BY_DATA_KEY_AND_CHART_TYPE_SQL.replace("#REPLACE#", timezone);
		StringBuffer sqlBuf = new StringBuffer(sql );
		sqlBuf.append(" limit ").append(limit).append(" offset ").append(start);
		jdbcTemplate.query(sqlBuf.toString(), 
		new PreparedStatementSetter() {
		    public void setValues(PreparedStatement ps)	throws SQLException {
				ps.setInt(1, dataId);
				ps.setString(2, chartType);
			}
		},
		new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				activityDataList.add(initActivityData(rs,timezoneNumber));
			}
		});
		return activityDataList;
	}
	
	
	//private  String  SELECT_ALL_SERIAL_NUMBER_BY_MONITOR_ID_AND_CHART_TYPE_SQL="select distinct serial_number from spc_activity a ,spc_discrete_data d where d.monitor_key=? and a.chart_type=? and a.spc_data_key = d.discrete_data_key " ;
	private  String  SELECT_ALL_SERIAL_NUMBER_BY_MONITOR_ID_AND_CHART_TYPE_SQL="select distinct serial_number from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_activity where monitor_key=? and chart_type=? " ;
	/**
     * get all serial number by monitor id and chart type in spc activity.
     * @param monitorId -int
     * @param chartType -String
     * @param serialNumberIterator -Iterator<String>
     * @return List<String>
	 * @throws DataAccessException
     */
	public List<String> getAllSerialNumberByMonitorIdAndChartType(final int monitorId, final String chartType, Iterator<String> serialNumberIterator) throws DataAccessException {
		final List<String> serialNumberList  = new ArrayList<String>();
		StringBuffer sb = new StringBuffer();
		sb.append(SELECT_ALL_SERIAL_NUMBER_BY_MONITOR_ID_AND_CHART_TYPE_SQL).append(" and serial_number in ('");
		while(serialNumberIterator.hasNext()){
			sb.append(serialNumberIterator.next()).append("','");
		}
		sb.delete(sb.length()-3, sb.length());
		sb.append("')");
		jdbcTemplate.query(
				sb.toString(),	
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, monitorId);
						ps.setString(2, chartType);
					}
				},
				 new RowCallbackHandler() {					
					public void processRow(ResultSet rs) throws SQLException {
						serialNumberList.add(rs.getString("serial_number"));
					}
				});
		return serialNumberList;
	}
	
	private static final String ADD_ACTIVITY_DATA_SQL =
		"INSERT INTO "+CommonServerUtil.getSpcSchemaPrefix()+"spc_activity ( spc_data_key,serial_key,serial_number,a_datetime,chart_type,defect_qty,container,monitor_key) VALUES (?,?,?,?,?,?,?,?)";
	/**
	 * add activity data.
	 * @param monitorId -int
	 * @param chartKey -int
	 * @param chartType -String
	 * @param activityList -List<ActivityDate>
	 * @return count -int
	 * @throws DataAccessException
	 */
	public int addActivityData(final int monitorId, final int chartKey,final String chartType,final List<ActivityData> activityList) throws DataAccessException  {
		final List<Integer> result = new ArrayList<Integer>();
		try {
		jdbcTemplate.batchUpdate(
			ADD_ACTIVITY_DATA_SQL, 
			new BatchPreparedStatementSetter() {
				public void setValues(PreparedStatement ps, int i) throws SQLException {
					ActivityData activityDate = activityList.get(i);
					ps.setLong(1, chartKey);
					ps.setLong(2, activityDate.getSerialKey());
					ps.setString(3, activityDate.getSn());
					ps.setLong(4, activityDate.getADataTime());
					ps.setString(5, chartType);
					ps.setDouble(6, activityDate.getDefects());
					ps.setString(7, activityDate.getContainer());
					ps.setInt(8, monitorId);
				}
			 
				public int getBatchSize() {
					result.add( activityList.size() );
					return activityList.size();
				}
		});
		}catch (Exception e){
			logger.error(e);
		}
		if ( result.size() > 0  ) {
			return result.get(0);
		} else {
			logger.error( "Can't addActivityData monitorId:"+monitorId + ";chartKey:"+chartKey +";chartType:"+chartType+";activityList:"+activityList.toString());
			return 0 ; 
		}
	}
	
	private ActivityData initActivityData(ResultSet rs ,String timezoneNumber) throws SQLException {
		ActivityData activityData = new ActivityData();
		activityData.setActivityId(rs.getInt("spc_activity_key"));
		activityData.setDataId(rs.getInt("spc_data_key"));
		activityData.setSerialKey(rs.getInt("serial_key"));
		activityData.setSn(rs.getString("serial_number"));
		activityData.setADataTime(rs.getInt("a_datetime"));
		activityData.setChartType(rs.getString("chart_type"));
		activityData.setDefects(rs.getInt("defect_qty"));
		activityData.setContainer(rs.getString("container"));
		activityData.setTime(rs.getTimestamp("time"));
		activityData.setTimeStr(activityData.getTime().toString().substring(0, 19) +timezoneNumber);
		return activityData;
	}
	
	private ActivityData initActivityData2(ResultSet rs) throws SQLException{
		ActivityData activityData = new ActivityData();
		activityData.setDataId(rs.getInt("spc_data_key"));
		activityData.setSerialKey(rs.getInt("serial_key"));
		activityData.setSn(rs.getString("serial_number"));
		activityData.setADataTime(rs.getInt("a_datetime"));
		activityData.setChartType(rs.getString("chart_type"));
		activityData.setDefects(rs.getInt("defect_qty"));
		activityData.setContainer(rs.getString("container"));
		return activityData;
	}

	private static final String SELECT_ACTIVITY_BY_OLDCHART_DATAKEY_SQL= "select * from "
			+CommonServerUtil.getSpcSchemaPrefix()+"spc_activity where chart_type=? and spc_data_key=?";
	public List<ActivityData> getNoDealDataFromActivity(final int datakey, final String oldChart) {
		final List<ActivityData> activityDataList  = new ArrayList<ActivityData>();
		jdbcTemplate.query(SELECT_ACTIVITY_BY_OLDCHART_DATAKEY_SQL, 
			new PreparedStatementSetter() {
			    public void setValues(PreparedStatement ps)	throws SQLException{
					ps.setString(1, oldChart);
					ps.setInt(2, datakey);
				}
			},
			new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					activityDataList.add(initActivityData2(rs) );
				}
			});
		return activityDataList;
	}

}